﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SQLite;

namespace program1
{
    public class DatabaseInitializer
    {
        private string connectionString = "Data Source=SchoolManagement.db";

        public void InitializeDatabase()
        {
            if (!System.IO.File.Exists("SchoolManagement.db"))
            {
                SQLiteConnection.CreateFile("SchoolManagement.db");
            }

            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    // 创建 Schools 表格
                    command.CommandText = @"
                        CREATE TABLE IF NOT EXISTS Schools (
                            SchoolID INTEGER PRIMARY KEY AUTOINCREMENT,
                            Name TEXT,
                            Location TEXT
                        );
                    ";
                    command.ExecuteNonQuery();

                    // 创建 Classes 表格
                    command.CommandText = @"
                        CREATE TABLE IF NOT EXISTS Classes (
                            ClassID INTEGER PRIMARY KEY AUTOINCREMENT,
                            SchoolID INTEGER,
                            Name TEXT,
                            Teacher TEXT,
                            FOREIGN KEY (SchoolID) REFERENCES Schools(SchoolID) ON DELETE CASCADE
                        );
                    ";
                    command.ExecuteNonQuery();

                    // 创建 Students 表格
                    command.CommandText = @"
                        CREATE TABLE IF NOT EXISTS Students (
                            StudentID INTEGER PRIMARY KEY AUTOINCREMENT,
                            ClassID INTEGER,
                            Name TEXT,
                            Age INTEGER,
                            Gender TEXT,
                            Address TEXT,
                            FOREIGN KEY (ClassID) REFERENCES Classes(ClassID) ON DELETE CASCADE
                        );
                    ";
                    command.ExecuteNonQuery();
                }
            }

            // 初始化示例数据
            InsertSampleData();
        }

        private void InsertSampleData()
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    // 插入示例学校数据
                    command.CommandText = "INSERT INTO Schools (Name, Location) VALUES ('School A', 'Location A');";
                    command.ExecuteNonQuery();

                    command.CommandText = "INSERT INTO Schools (Name, Location) VALUES ('School B', 'Location B');";
                    command.ExecuteNonQuery();

                    // 插入示例班级数据
                    command.CommandText = "INSERT INTO Classes (SchoolID, Name, Teacher) VALUES (1, 'Class 101', 'Teacher A');";
                    command.ExecuteNonQuery();

                    command.CommandText = "INSERT INTO Classes (SchoolID, Name, Teacher) VALUES (2, 'Class 201', 'Teacher B');";
                    command.ExecuteNonQuery();

                    // 插入示例学生数据
                    command.CommandText = "INSERT INTO Students (ClassID, Name, Age, Gender, Address) VALUES (1, 'Student 1', 18, 'Male', 'Address 1');";
                    command.ExecuteNonQuery();

                    command.CommandText = "INSERT INTO Students (ClassID, Name, Age, Gender, Address) VALUES (2, 'Student 2', 17, 'Female', 'Address 2');";
                    command.ExecuteNonQuery();
                }
            }
        }
    }
}
